const mysql = require('mysql2/promise');
const XLSX = require('xlsx');
const dotenv = require('dotenv');
const path = require('path');

dotenv.config();

const dbConfig = {
  host: process.env.DB_HOST || "localhost",
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "YUsheng7989",
  database: process.env.DB_NAME || "327test1",
};

const excelFilePath = path.join(__dirname, '第一课.xlsx');

async function importExcelToDatabase() {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');
    
    const workbook = XLSX.readFile(excelFilePath);
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];
    
    const jsonData = XLSX.utils.sheet_to_json(worksheet, {
      header: ['japanese', 'ruby', 'chinese', 'course'],
      raw: false,
      defval: ''
    });
    
    const dataToInsert = jsonData.slice(1);
    
    console.log(`📄 读取到 ${dataToInsert.length} 条记录`);
    
    if (dataToInsert.length === 0) {
      console.log('❌ 没有数据可导入');
      return;
    }
    
    await connection.beginTransaction();
    
    const query = 'INSERT INTO class (id, japanese, ruby, chinese, course) VALUES ?';
    const values = dataToInsert.map((item, index) => [
      index + 1,
      item.japanese, 
      item.ruby, 
      item.chinese,
      item.course
    ]);
    
    const [result] = await connection.query(query, [values]);
    
    await connection.commit();
    
    console.log(`✅ 成功导入 ${result.affectedRows} 条记录`);
    
  } catch (error) {
    if (connection) {
      await connection.rollback();
    }
    
    console.error('❌ 导入过程中发生错误:', error);
    
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

importExcelToDatabase().catch(err => {
  console.error('❌ 程序执行出错:', err);
});